﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.ProviderBase;
using System.Data.Sql;
using System.Data.SqlClient;

namespace NhaCungCap.DAO
{
    public class DataProvider
    {
        private static SqlConnection con = null;
        static DataProvider()
        {
            //string strConnection = "Data Source=(local)\\SQLEXPRESS;AttachDbFilename=|DataDirectory|NhaXuatBan_v3_3.mdf;Integrated Security=True;User Instance=True";

            string strConnection = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\NhaXuatBan_v3_3.mdf;Integrated Security=True;User Instance=True";
            //string strConnection = "Data Source=(local)\\SQLEXPRESS;Initial Catalog=NhaXuatBan_v3_3;User ID=sa;Password=;Integrated Security=True";
            con = new SqlConnection(strConnection);

        }

        public void Disconnect()
        {
            con.Close();
        }
        public DataTable ExecuteQuery(string strQuery)
        {
            DataTable dataTable = null;

            try
            {
                con.Open();

                SqlDataAdapter sqlAdapter = new SqlDataAdapter(strQuery, con);
                dataTable = new DataTable();
                sqlAdapter.Fill(dataTable);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return dataTable;
        }

        public bool ExecuteNonQuery(string strQuery)
        {
            bool kq = false;
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(strQuery, con);
                int sl = cmd.ExecuteNonQuery();
                kq = true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return kq;
        }
        public DataTable ExecuteStoreProc(string storeProcName, IList<string> arrParameterName, ArrayList arrParameterValue)
        {
            DataTable resTable = null;

            try
            {
                con.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = storeProcName;
                cmd.CommandType = CommandType.StoredProcedure;

                //Lay so parameter
                int N = arrParameterName.Count;
                for (int i = 0; i < N; i++)
                {
                    SqlParameter sqlParam = new SqlParameter(arrParameterName[i], arrParameterValue[i]);
                    cmd.Parameters.Add(sqlParam);
                }

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(resTable);

                con.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Loi khi thuc thi store procedure: " + ex.Message);
            }

            return resTable;
        }

        //public static string providerName { get; set; }
    }
}